package cn.js.sangu.unitStore.mapper;

import java.util.List;

import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;

import cn.js.sangu.unitStore.entity.ExcelData;

public interface ExcelDataMapper extends BaseMapper<ExcelData> {
    @Insert("<script>INSERT INTO excel_bom (xuhao, tuhao, name, guige, cailiao, cengjishu, zongjishu, danzhong, zongzhong, leibie, bianma, beizhu, xitong, shuchu, syscode, remark) VALUES " +
            "<foreach collection='list' item='item' separator=','>" +
            "(#{item.xuhao}, #{item.tuhao}, #{item.name}, #{item.guige}, #{item.cailiao}, #{item.cengjishu,jdbcType=INTEGER}, " +
            "#{item.zongjishu,jdbcType=INTEGER}, #{item.danzhong,jdbcType=DECIMAL}, #{item.zongzhong,jdbcType=DECIMAL}, #{item.leibie}, #{item.bianma}, #{item.beizhu}, #{item.xitong}, #{item.shuchu}, #{item.syscode}, #{item.remark}) " +
            "</foreach></script>")
    void batchInsert(List<ExcelData> list);

    void filterDataToTemp();

    @Insert("INSERT INTO excel_bom_11 (xuhao, tuhao, name, guige, cailiao, zongjishu, danzhong, zongzhong, leibie, bianma, beizhu, xitong, shuchu, syscode, remark) " +
            " SELECT " +
            "    xuhao," +
            "    tuhao," +
            "    name," +
            "    guige," +
            "    cailiao," +
            "    zongjishu," +
            "    danzhong," +
            "    zongzhong," +
            "    leibie," +
            "    bianma," +
            "    beizhu," +
            "    xitong," +
            "    shuchu," +
            "    syscode," +
            "    remark" +
            " FROM " +
            "    excel_bom e " +
            " WHERE " +
            "    (leibie IN ('钣金件', '机加件', '组件') OR (leibie = '标准件' AND  xitong in ('有图','出图','附图')))" +
        //     "    (leibie IN ('钣金件', '机加件', '组件') OR (leibie = '标准件' AND (beizhu LIKE '%出图%' OR beizhu LIKE '%有图%' OR xitong LIKE '%出图%' OR xitong LIKE '%有图%')))" +
        //     "    (leibie IN ('钣金件', '机加件', '组件') OR (leibie = '标准件' AND MATCH(beizhu, xitong) AGAINST('出图 有图' IN BOOLEAN MODE)))" +
            " GROUP BY " +
            "    tuhao, name, guige, zongjishu ORDER BY tuhao,guige")
    void insertDataToExcel11();

    @Insert("INSERT INTO excel_bom_12 (xuhao, tuhao, name, guige, cailiao, zongjishu, danzhong, zongzhong, leibie, bianma, beizhu, xitong, shuchu, syscode, remark) " +
            " SELECT " +
            "    xuhao," +
            "    tuhao," +
            "    name," +
            "    guige," +
            "    cailiao," +
            "    zongjishu," +
            "    danzhong," +
            "    zongzhong," +
            "    leibie," +
            "    bianma," +
            "    beizhu," +
            "    xitong," +
            "    shuchu," +
            "    syscode," +
            "    remark" +
            " FROM " +
            "    excel_bom e " +
            " WHERE " +
            "    leibie = '标准件'" +
            " GROUP BY " +
            "    tuhao, name, guige, zongjishu ORDER BY tuhao,guige")
    void insertDataToExcel12();

    @Select("SELECT * FROM excel_bom_11 WHERE bianma IN (SELECT bianma FROM excel_bom_11 GROUP BY bianma HAVING COUNT(*) > 1)")
    List<ExcelData> findDuplicateCodesBy11();

    @Select("SELECT * FROM excel_bom_11 WHERE bianma = #{bianma}")
    List<ExcelData> findByCodeBy11(String bianma);

    @Select("SELECT * FROM excel_bom_11")
    List<ExcelData> findBy11();

    @Update("UPDATE excel_bom_11 SET shuchu = #{shuchu} WHERE id = #{id}")
    void updateShuchuBy11(ExcelData data);

    @Select("SELECT * FROM excel_bom_12 WHERE bianma IN (SELECT bianma FROM excel_bom_11 GROUP BY bianma HAVING COUNT(*) > 1)")
    List<ExcelData> findDuplicateCodesBy12();

    @Select("SELECT * FROM excel_bom_12 WHERE bianma = #{bianma}")
    List<ExcelData> findByCodeBy12(String bianma);

    @Update("UPDATE excel_bom_12 SET shuchu = #{shuchu} WHERE id = #{id}")
    void updateShuchuBy12(ExcelData data);

    @Select("SELECT * FROM excel_bom_12")
    List<ExcelData> findBy12();

    @Delete("truncate table excel_bom")
    void deleteExcelData();

    @Delete("truncate table excel_bom_11")
    void deleteExcel11Data();

    @Delete("truncate table excel_bom_12")
    void deleteExcel12Data();
    
    @Update("ALTER TABLE excel_bom_11 AUTO_INCREMENT = 1;")
    void resetAutoIncrement11();

    @Update("ALTER TABLE excel_bom_12 AUTO_INCREMENT = 1;")
    void resetAutoIncrement12();


    @Update("<script>" +
            "UPDATE excel_bom_11 SET shuchu = CASE id " +
            "<foreach collection='list' item='item'>" +
            "WHEN #{item.id} THEN #{item.shuchu} " +
            "</foreach>" +
            "END " +
            "WHERE id IN " +
            "<foreach collection='list' item='item' open='(' separator=',' close=')'>" +
            "#{item.id}" +
            "</foreach>" +
            "<if test='list != null and list.size() > 0'> </if>" + // 空列表占位（避免语法错误）
            "</script>")
    void batchUpdateShuchuBy11(List<ExcelData> list);

    @Update("<script>" +
            "UPDATE excel_bom_12 SET shuchu = CASE id " +
            "<foreach collection='list' item='item'>" +
            "WHEN #{item.id} THEN #{item.shuchu} " +
            "</foreach>" +
            "END " +
            "WHERE id IN " +
            "<foreach collection='list' item='item' open='(' separator=',' close=')'>" +
            "#{item.id}" +
            "</foreach>" +
            "<if test='list != null and list.size() > 0'> </if>" + // 空列表占位（避免语法错误）
            "</script>")
    void batchUpdateShuchuBy12(List<ExcelData> list);

    int updateBatchById(@Param("list") List<ExcelData> list);

        @Update("<script>" +
                "UPDATE excel_bom_11 SET syscode = CASE id " +
                "<foreach collection='list' item='item'>" +
                "WHEN #{item.id} THEN #{item.syscode} " +
                "</foreach>" +
                "END " +
                "WHERE id IN " +
                "<foreach collection='list' item='item' open='(' separator=',' close=')'>" +
                "#{item.id}" +
                "</foreach>" +
                "</script>")
        void batchUpdateSyscodeBy11(List<ExcelData> list);

        @Update("<script>" +
                "UPDATE excel_bom_12 SET syscode = CASE id " +
                "<foreach collection='list' item='item'>" +
                "WHEN #{item.id} THEN #{item.syscode} " +
                "</foreach>" +
                "END " +
                "WHERE id IN " +
                "<foreach collection='list' item='item' open='(' separator=',' close=')'>" +
                "#{item.id}" +
                "</foreach>" +
                "</script>")
        void batchUpdateSyscodeBy12(List<ExcelData> list);

        // 新增syscode查重相关SQL
        @Select("SELECT * FROM excel_bom_11 WHERE syscode IN (SELECT syscode FROM excel_bom_11 GROUP BY syscode HAVING COUNT(*) > 1)")
        List<ExcelData> findDuplicateSyscodesBy11();

        @Select("SELECT * FROM excel_bom_11 WHERE syscode = #{syscode}")
        List<ExcelData> findBySyscodeBy11(String syscode);

        @Update("<script>UPDATE excel_bom_11 SET remark = CASE id " +
        "<foreach collection='list' item='item'>WHEN #{item.id} THEN #{item.remark}</foreach> " +
        "END WHERE id IN <foreach collection='list' item='item' open='(' separator=',' close=')'>#{item.id}</foreach></script>")
        void batchUpdateRemarkBy11(List<ExcelData> list);

        // 新增syscode查重相关SQL
        @Select("SELECT * FROM excel_bom_12 WHERE syscode IN (SELECT syscode FROM excel_bom_12 GROUP BY syscode HAVING COUNT(*) > 1)")
        List<ExcelData> findDuplicateSyscodesBy12();

        @Select("SELECT * FROM excel_bom_12 WHERE syscode = #{syscode}")
        List<ExcelData> findBySyscodeBy12(String syscode);

        @Update("<script>UPDATE excel_bom_12 SET remark = CASE id " +
        "<foreach collection='list' item='item'>WHEN #{item.id} THEN #{item.remark}</foreach> " +
        "END WHERE id IN <foreach collection='list' item='item' open='(' separator=',' close=')'>#{item.id}</foreach></script>")
        void batchUpdateRemarkBy12(List<ExcelData> list);


}

    